9.11 查询操作

📝 模块更新日志 新特性*

+ 新增 `EFCore` 查询 `.WhereCase` 拓展方法 4\.9\.4\.4 ⏱️2024\.07\.10 [61ff90a](https://gitee.com/dotnetchina/Furion/commit/61ff90aa82d988323e9268f2cd9085a99ab0347e)
+ 新增 拓展 `EFCore` 排序功能，支持带条件进行排序 4\.9\.3\.20 ⏱️2024\.06\.11 [889e35a](https://gitee.com/dotnetchina/Furion/commit/889e35a0f2f1db0946a6af676f92632e42dd3042)

• 突破性变化

  • 调整 EFCore 带条件 Include 返回值类型，IIncludableQueryable<TSource, TProperty> -> IQueryable<TSource> 4.9.3.20 ⏱️2024.06.11 889e35a

  • 问题修复

  • 修复 因 889e35a 功能引起的 EFCore 排序新异常问题 4.9.4 ⏱️2024.06.11 9271528

9.11.1 根据主键查询一条​

// ==== 同步操作 ====  

// 示例一  
var person = repository.Find(1);  

// 示例二  
var person = repository.FindOrDefault(1);  

// 示例三  
var person = repository.Entities.Find(1);  

// ==== 异步操作 ====  

// 示例一  
var person = await repository.FindAsync(1);  

// 示例二  
var person = await repository.FindOrDefaultAsync(1);  

// 示例三  
var person = await repository.Entities.FindAsync(1);  

小提示可以支持多个键查询，如主键、联合键。repository.Find(1, "百小僧");

9.11.2 根据条件查询一条​

// ==== 同步操作 ====  

// 示例一  
var person = repository.Single(u => u.Name == "Furion");  

// 示例二  
var person = repository.SingleOrDefault(u => u.Name == "Furion");  

// 示例三  
var person = repository.First(u => u.Name == "Furion");  

// 示例四  
var person = repository.FirstOrDefault(u => u.Name == "Furion");  

// 示例五  
var person = repository.Last(u => u.Name == "Furion");  

// 示例六  
var person = repository.LastOrDefault(u => u.Name == "Furion");  

// 示例七  
var person = repository.Entities.Single(u => u.Name == "Furion");  

// 示例八  
var person = repository.Entities.First(u => u.Name == "Furion");  

// 示例九  
var person = repository.Entities.FirstOrDefault(u => u.Name == "Furion");  

// 示例十  
var person = repository.Entities.Last(u => u.Name == "Furion");  

// 示例十一  
var person = repository.Entities.LastOrDefault(u => u.Name == "Furion");  

// ==== 异步操作 ====  

// 示例一  
var person = await repository.SingleAsync(u => u.Name == "Furion");  

// 示例二  
var person = await repository.SingleOrDefaultAsync(u => u.Name == "Furion");  

// 示例三  
var person = await repository.FirstAsync(u => u.Name == "Furion");  

// 示例四  
var person = await repository.FirstOrDefaultAsync(u => u.Name == "Furion");  

// 示例五  
var person = await repository.LastAsync(u => u.Name == "Furion");  

// 示例六  
var person = await repository.LastOrDefaultAsync(u => u.Name == "Furion");  

// 示例七  
var person = await repository.Entities.SingleAsync(u => u.Name == "Furion");  

// 示例八  
var person = await repository.Entities.FirstAsync(u => u.Name == "Furion");  

// 示例九  
var person = await repository.Entities.FirstOrDefaultAsync(u => u.Name == "Furion");  

// 示例十  
var person = await repository.Entities.LastAsync(u => u.Name == "Furion");  

// 示例十一  
var person = await repository.Entities.LastOrDefaultAsync(u => u.Name == "Furion");  

9.11.3 查询所有数据​

// ==== 同步操作 ====  

// 示例一  
var persons = repository.Entities;  

// 示例二  
var persons = repository.DetachedEntities;  

// 示例三  
var persons = repository.AsQueryable();  

// 示例四  
var persons = repository.AsEnumerable();  

// 示例五  
var persons = await repository.AsQueryable().ToListAsync();  

9.11.4 根据条件查询所有数据​

// 示例一  
var persons = repository.Where(u => u.Id > 10);  

// 示例二  
var persons = repository.Where(u => u.Id > 10 && u.Name.Equals("Furion"));  

// 示例三  (多个 where 里是 "并且"，Id>10 and Name == "Furion")  
var persons = repository.Where(u => u.Id > 10).Where(u => u.Name.Equals("Furion"));  

// 示例四 (判断 name 是否有值，如果 name 是空的则不会执行 u => u.Id > 10 && u.Name.Equals("Furion")，如果 name 有值就会执行 u => u.Id > 10 && u.Name.Equals("Furion"))  
var persons = repository.Where(!string.IsNullOrEmpty(name), u => u.Id > 10 && u.Name.Equals("Furion"));  

// 示例五  
var persons = repository.Where(!string.IsNullOrEmpty(name), u => u.Id > 10)  
                        .Where(age > 18, u => u.Name.Contains("百小僧"))  
                        .Where(u => u.Age > 18);  

// 示例六 (在一个 where 里用逗号分隔是 "或者"，Name=="Furion" or Name == "百小僧" or Name == "MonkSoul")  
var persons = repository.Where(u => u.Name == "Furion",  
                                 u => u.Name == "百小僧",  
                                 u => u.Name == "MonkSoul");  

// 示例七  
var persons = repository.Where(u => u.Id > 10).Where(u => u.Name.Equals("Furion"))  
                        .Where(age > 18, u => u.Name.Contains("百小僧"))  
                        .Where(u => u.Name == "Furion",  
                                 u => u.Name == "百小僧",  
                                 u => u.Name == "MonkSoul");  

// 示例八  
var persons = repository.Where((age > 18, u => u.Name == "Furion"),  
                                   (!string.IsNullOrEmpty(name), u => u.Id > 10));  

// 示例九  
var persons = repository.Where(u => u.Id > 10).Where(u => u.Name.Equals("Furion"))  
                        .Where(age > 18, u => u.Name.Contains("百小僧"))  
                        .Where(u => u.Name == "Furion",  
                                 u => u.Name == "百小僧",  
                                 u => u.Name == "MonkSoul")  
                        .Where((age > 18, u => u.Name == "Furion"),  
                                   (!string.IsNullOrEmpty(name), u => u.Id > 10));  

// 示例十  
var persons = repository.Entities.Where(u => u.Id > 10)  
                                 .Where(age > 18, u => u.Name.Contains("百小僧"));  

// 示例十一  
var persons = repository.DetachedEntities.Where(u => u.Id > 20);  

// 示例十二  
var persons = repository.AsQueryable(u => u.Id > 20).Where(u => u.Name == "Furion");  

9.11.5 分页查询​

// ==== 同步操作 ====  

// 示例一  
var persons = repository.Where(u => u.Id > 10).ToPagedList();  

// 示例二  
var persons = repository.Where(u => u.Id > 10).ToPagedList(1, 10);  

// ==== 异步操作 ====  

// 示例一  
var persons = await repository.Where(u => u.Id > 10).ToPagedListAsync();  

// 示例二  
var persons = await repository.Where(u => u.Id > 10).ToPagedListAsync(1, 10);  

9.11.6 其他查询​

9.11.6.1 查看记录是否存在​

// ==== 同步操作 ====  

// 示例一  
var isExists = repository.Any();  

// 示例二  
var isExists = repository.Any(u => u.Id > 10);  

// 示例三  
var isExists = repository.Entities.Any();  

// 示例四  
var isExists = repository.DetachedEntities.Any(u => u.Id > 10);  

// 示例五  
var isExists = repository.Where(u => u.Id > 10).Any();  

// ==== 异步操作 ====  

// 示例一  
var isExists = await repository.AnyAsync();  

// 示例二  
var isExists = await repository.AnyAsync(u => u.Id > 10);  

// 示例三  
var isExists = await repository.Entities.AnyAsync();  

// 示例四  
var isExists = await repository.DetachedEntities.AnyAsync(u => u.Id > 10);  

// 示例五  
var isExists = await repository.Where(u => u.Id > 10).AnyAsync();  

9.11.6.2 查询记录数​

// ==== 同步操作 ====  

// 示例一  
var count = repository.Count();  

// 示例二  
var count = repository.Count(u => u.Id > 10);  

// 示例三  
var count = repository.Entities.Count(u => u.Id > 10);  

// 示例四  
var count = repository.Entities.DetachedEntities.Count();  

// 示例五  
var count = repository.Where(u => u.Id > 10).Count();  

// ==== 异步操作 ====  

// 示例一  
var count = await repository.CountAsync();  

// 示例二  
var count = await repository.CountAsync(u => u.Id > 10);  

// 示例三  
var count = await repository.Entities.CountAsync(u => u.Id > 10);  

// 示例四  
var count = await repository.Entities.DetachedEntities.CountAsync();  

// 示例五  
var count = await repository.Where(u => u.Id > 10).CountAsync();  

9.11.6.3 查询最大值​

// ==== 同步操作 ====  

// 示例一  
var entity = repository.Max();  

// 示例二  
var value = repository.Max(u => u.Id);  

// 示例三  
var entity = repository.Entities.Max();  

// 示例四  
var value = repository.Entities.DetachedEntities.Max(u => u.Age);  

// 示例五  
var value = repository.Where(u => u.Id > 10).Max(u => u.Age);  

// ==== 异步操作 ====  

// 示例一  
var entity = await repository.MaxAsync();  

// 示例二  
var value = await repository.MaxAsync(u => u.Id);  

// 示例三  
var entity = await repository.Entities.MaxAsync();  

// 示例四  
var value = await repository.Entities.DetachedEntities.MaxAsync(u => u.Age);  

// 示例五  
var value = await repository.Where(u => u.Id > 10).MaxAsync(u => u.Age);  

9.11.6.4 查询最小值​

// ==== 同步操作 ====  

// 示例一  
var entity = repository.Min();  

// 示例二  
var value = repository.Min(u => u.Id);  

// 示例三  
var entity = repository.Entities.Min();  

// 示例四  
var value = repository.Entities.DetachedEntities.Min(u => u.Age);  

// 示例五  
var value = repository.Where(u => u.Id > 10).Min(u => u.Age);  

// ==== 异步操作 ====  

// 示例一  
var entity = await repository.MinAsync();  

// 示例二  
var value = await repository.MinAsync(u => u.Id);  

// 示例三  
var entity = await repository.Entities.MinAsync();  

// 示例四  
var value = await repository.Entities.DetachedEntities.MinAsync(u => u.Age);  

// 示例五  
var value = await repository.Where(u => u.Id > 10).MinAsync(u => u.Age);  

9.11.6.5 求和查询​

// ==== 同步操作 ====  

// 示例一  
var sum = repository.Entities.Sum(u => u.Cost);  

// 示例二  
var sum = repository.AsQueryable().Sum(u => u.Cost);  

// 示例三  
var sum = repository.DetachedEntities.Sum(u => u.Cost);  

// ==== 异步操作 ====  

// 示例一  
var sum = await repository.Entities.SumAsync(u => u.Cost);  

// 示例二  
var sum = await repository.AsQueryable().SumAsync(u => u.Cost);  

// 示例三  
var sum = await repository.DetachedEntities.SumAsync(u => u.Cost);  

9.11.6.6 求平均值查询​

// ==== 同步操作 ====  

// 示例一  
var sum = repository.Entities.Average(u => u.Cost);  

// 示例二  
var sum = repository.AsQueryable().Average(u => u.Cost);  

// 示例三  
var sum = repository.DetachedEntities.Average(u => u.Cost);  

// ==== 异步操作 ====  

// 示例一  
var sum = await repository.Entities.AverageAsync(u => u.Cost);  

// 示例二  
var sum = await repository.AsQueryable().AverageAsync(u => u.Cost);  

// 示例三  
var sum = await repository.DetachedEntities.AverageAsync(u => u.Cost);  

9.11.6.7 时间查询​

var starDate = DateTime.Parse("2020-09-10");  
var endDate = DateTime.Parse("2020-09-10");  
var query = repository.Where(u => u.CreatedDt >= starDate && u.CreatedDt <= endDate);  

9.11.6.8 模糊查询​

// 示例一  
repository.Where(u => u.Name.StartsWith("Furion"));  

// 示例二  
_testRepository.Where(u => u.Name.EndsWith("Furion"));  

// 示例三  
_testRepository.Where(u => u.Name.Contains("Furion"));  

9.11.6.9 Case When​

数据库中的 Case When 实际上对应的是我们程序中的 三元表达式 ，也就是使用 三元表达式 即可自动生成 Case When 语句。

在 Furion 4.9.4.4+ 版本中新增了 .WhereCase 拓展，如：

当 publicity 为 bool? 类型时：

.WhereCase(publicity,   
    u => u.Publicity,                       // publicity 为 true 时  
    u => !u.Publicity && u.UserId == currentUserId, // publicity 为 false 时  
    u => u.UserId == currentUserId || u.Publicity); // publicity 为 null 时  

当 publicity 为 bool 类型时：

.WhereCase(publicity,   
    u => u.Publicity,                       // publicity 为 true 时  
    u => !u.Publicity && u.UserId == currentUserId); // publicity 为 false 时  

9.11.7 反馈与建议​

与我们交流给 Furion 提 Issue。